package com.kadron.datamodel;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.kadron.common.KUtil;
import com.kadron.common.KeyValue;
import com.kadron.dao.KOrderDAO;
import com.kadron.entity.Item;
import com.kadron.order.SearchOrder;

public class KOrderDataProvider implements KDataProvider {

	/**
	 * 
	 */
	private static final long serialVersionUID = -4707252300895840420L;
	
	private KModelCreator creator;
	private KOrderDAO orderDAO;
	private SearchOrder search;
	
	public KOrderDataProvider(SearchOrder search,KModelCreator creator, KOrderDAO orderDAO) {
		super();
		this.creator = creator;
		this.search = search;
		this.orderDAO = orderDAO;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> getItemsByRange(Integer integer, int numberOfRows,
			Object object, boolean b) {
		EntityManager em = orderDAO.getEntityManager();
		List<KeyValue> params = new ArrayList<KeyValue>();
		StringBuilder builder = new StringBuilder("SELECT DISTINCT o.id FROM Order o WHERE o.id > 0 ");
		if (!KUtil.isEmpty(search.getApplication())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item.application a WHERE oi.order.id = o.id AND (a.name_en LIKE :app_name OR a.name_es  LIKE :app_name OR a.name_sr LIKE :app_name))");
			params.add(new KeyValue("app_name", search.getApplication()+"%"));
		}
		if (!KUtil.isEmpty(search.getItemType())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item.itemType it WHERE oi.order.id = o.id AND (it.name_en LIKE :it_name OR it.name_es  LIKE :it_name OR it.name_sr LIKE :it_name ))");
			params.add(new KeyValue("it_name", search.getItemType()+"%"));
		}
		if (!KUtil.isEmpty(search.getItemName())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item i WHERE oi.order.id = o.id AND (i.name_en LIKE :itemName OR i.name_es  LIKE :itemName OR i.name_sr LIKE :itemName))");
			params.add(new KeyValue("itemName", search.getItemName()+"%"));
		}
		if (search.getOrderFrom() != null) {
			builder.append(" AND o.created >= :orderFrom");
			params.add(new KeyValue("orderFrom", search.getOrderFrom()));
		}
		if (search.getOrderTo() != null) {
			builder.append(" AND o.created <= :orderTo");
			params.add(new KeyValue("orderTo", search.getOrderTo()));
		}
		if (search.getStatus() != null) {
			builder.append(" AND o.status = :status");
			params.add(new KeyValue("status", search.getStatus()));
		}
		if (search.getCurrency() != null) {
			builder.append(" AND o.currency.id = :cid ");
			params.add(new KeyValue("cid", search.getCurrency().getId()));
		}
		if (!KUtil.isEmpty(search.getUsername())) {
			builder.append(" AND EXISTS(SELECT 1 FROM User u WHERE u.id = o.user.id AND u.username LIKE :username)");
			params.add(new KeyValue("username", search.getUsername()+"%"));
		}
		if (search.getValidFrom() != null || search.getValidTo() != null) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item i WHERE oi.order.id = o.id ");
			if (search.getValidFrom() != null) {
				builder.append(" AND  i.validFrom >= :validFrom ");				
				params.add(new KeyValue("validFrom", search.getValidFrom()));
			}
			if (search.getValidTo() != null) {
				builder.append(" AND  i.validTo >= :validTo ");								
				params.add(new KeyValue("validTo", search.getValidTo()));
			}
			builder.append(")");
		}
		if (search.getPriceFrom() != null || search.getPriceTo() != null) {
			if (search.getPriceFrom() != null) {
				builder.append(" AND :priceFrom <= (SELECT SUM(oi.price*oi.quantity) FROM OrderItem oi WHERE oi.order.id=o.id) ");				
				params.add(new KeyValue("priceFrom", search.getPriceFrom()));
			}
			if (search.getPriceTo() != null) {
				builder.append(" AND (SELECT SUM(oi.price*oi.quantity) FROM OrderItem oi WHERE oi.order.id=o.id) <= :priceTo ");								
				params.add(new KeyValue("priceTo", search.getPriceTo()));
			}
		}
		builder.append(" ORDER BY o.id DESC ");
		Query query = em.createQuery(builder.toString());
		for (KeyValue kv : params) {
			query.setParameter(kv.getKey(), kv.getValue());
		}
		List<Long> list = query.setFirstResult(integer).setMaxResults(numberOfRows).getResultList();
		return list;
	}

	@Override
	public KModel getItemById(Long id, KModelCreator creator) {
		return creator.create(id);
	}

	@Override
	public int getRowCount() {
		EntityManager em = orderDAO.getEntityManager();
		List<KeyValue> params = new ArrayList<KeyValue>();
		StringBuilder builder = new StringBuilder("SELECT COUNT(DISTINCT o.id) FROM Order o WHERE o.id > 0 ");
		if (!KUtil.isEmpty(search.getApplication())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item.application a WHERE oi.order.id = o.id AND (a.name_en LIKE :app_name OR a.name_es  LIKE :app_name OR a.name_sr LIKE :app_name))");
			params.add(new KeyValue("app_name", search.getApplication()+"%"));
		}
		if (!KUtil.isEmpty(search.getItemType())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item.itemType it WHERE oi.order.id = o.id AND (it.name_en LIKE :it_name OR it.name_es  LIKE :it_name OR it.name_sr LIKE :it_name ))");
			params.add(new KeyValue("it_name", search.getItemType()+"%"));
		}
		if (!KUtil.isEmpty(search.getItemName())) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item i WHERE oi.order.id = o.id AND (i.name_en LIKE :itemName OR i.name_es  LIKE :itemName OR i.name_sr LIKE :itemName))");
			params.add(new KeyValue("itemName", search.getItemName()+"%"));
		}
		if (search.getOrderFrom() != null) {
			builder.append(" AND o.created >= :orderFrom");
			params.add(new KeyValue("orderFrom", search.getOrderFrom()));
		}
		if (search.getOrderTo() != null) {
			builder.append(" AND o.created <= :orderTo");
			params.add(new KeyValue("orderTo", search.getOrderTo()));
		}
		if (search.getStatus() != null) {
			builder.append(" AND o.status = :status");
			params.add(new KeyValue("status", search.getStatus()));
		}
		if (search.getCurrency() != null) {
			builder.append(" AND o.currency.id = :cid ");
			params.add(new KeyValue("cid", search.getCurrency().getId()));
		}
		if (!KUtil.isEmpty(search.getUsername())) {
			builder.append(" AND EXISTS(SELECT 1 FROM User u WHERE u.id = o.user.id AND u.username LIKE :username)");
			params.add(new KeyValue("username", search.getUsername()+"%"));
		}
		if (search.getValidFrom() != null || search.getValidTo() != null) {
			builder.append(" AND EXISTS(SELECT 1 FROM OrderItem oi INNER JOIN oi.item i WHERE oi.order.id = o.id ");
			if (search.getValidFrom() != null) {
				builder.append(" AND  i.validFrom >= :validFrom ");				
				params.add(new KeyValue("validFrom", search.getValidFrom()));
			}
			if (search.getValidTo() != null) {
				builder.append(" AND  i.validTo <= :validTo ");								
				params.add(new KeyValue("validTo", search.getValidTo()));
			}
			builder.append(")");
		}
		if (search.getPriceFrom() != null || search.getPriceTo() != null) {
			if (search.getPriceFrom() != null) {
				builder.append(" AND :priceFrom <= (SELECT SUM(oi.price*oi.quantity) FROM OrderItem oi WHERE oi.order.id=o.id) ");				
				params.add(new KeyValue("priceFrom", search.getPriceFrom()));
			}
			if (search.getPriceTo() != null) {
				builder.append(" AND (SELECT SUM(oi.price*oi.quantity) FROM OrderItem oi WHERE oi.order.id=o.id) <= :priceTo ");								
				params.add(new KeyValue("priceTo", search.getPriceTo()));
			}	
		}
		Query query = em.createQuery(builder.toString());
		for (KeyValue kv : params) {
			query.setParameter(kv.getKey(), kv.getValue());
		}
		Long count = (Long) query.getSingleResult();
		return (count == null) ? 0 : count.intValue() ;
	}

	@Override
	public boolean hasItemWithId(Long id) {
		return orderDAO.exists(id, Item.class);
	}

	@Override
	public KModelCreator getCreator() {
		return creator;
	}

	public SearchOrder getSearch() {
		return search;
	}

	public void setSearch(SearchOrder search) {
		this.search = search;
	}

}
